package util;

import java.sql.*;

/**
 * 数据库初始化工具类
 */
public class DatabaseInitializer {

    /**
     * 初始化数据库表结构
     */
    public static void initDatabase() {
        try {
            Connection conn = DatabaseConnection.getConnection();

            // 创建用户表
            createUsersTable(conn);

            // 创建座位表
            createSeatsTable(conn);

            // 创建预约表
            createReservationsTable(conn);

            // 创建时间段表
            createTimeSlotsTable(conn);

            // 插入初始数据
            insertInitialData(conn);

            System.out.println("数据库初始化完成！");

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("数据库初始化失败: " + e.getMessage());
        }
    }

    private static void createUsersTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                "user_id INT AUTO_INCREMENT PRIMARY KEY," +
                "student_id VARCHAR(20) UNIQUE NOT NULL," +
                "name VARCHAR(50) NOT NULL," +
                "password VARCHAR(100) NOT NULL," +
                "phone VARCHAR(20)," +
                "email VARCHAR(100)," +
                "user_type INT DEFAULT 0 COMMENT '0:学生 1:管理员'," +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.executeUpdate();
        }
    }

    private static void createSeatsTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS seats (" +
                "seat_id INT AUTO_INCREMENT PRIMARY KEY," +
                "seat_number VARCHAR(10) UNIQUE NOT NULL," +
                "floor INT NOT NULL," +
                "area VARCHAR(50) NOT NULL," +
                "status INT DEFAULT 0 COMMENT '0:可用 1:已预约 2:维护中'," +
                "equipment TEXT," +
                "has_computer BOOLEAN DEFAULT FALSE," +
                "has_power_socket BOOLEAN DEFAULT TRUE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.executeUpdate();
        }
    }

    private static void createReservationsTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS reservations (" +
                "reservation_id INT AUTO_INCREMENT PRIMARY KEY," +
                "user_id INT NOT NULL," +
                "seat_id INT NOT NULL," +
                "reservation_date DATE NOT NULL," +
                "start_time TIME NOT NULL," +
                "end_time TIME NOT NULL," +
                "status INT DEFAULT 0 COMMENT '0:有效 1:已取消 2:已完成'," +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE," +
                "FOREIGN KEY (seat_id) REFERENCES seats(seat_id) ON DELETE CASCADE," +
                "INDEX idx_user_date (user_id, reservation_date)," +
                "INDEX idx_seat_date (seat_id, reservation_date)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.executeUpdate();
        }
    }

    private static void createTimeSlotsTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS time_slots (" +
                "slot_id INT AUTO_INCREMENT PRIMARY KEY," +
                "slot_name VARCHAR(20) NOT NULL," +
                "start_time TIME NOT NULL," +
                "end_time TIME NOT NULL," +
                "is_active BOOLEAN DEFAULT TRUE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.executeUpdate();
        }
    }

    private static void insertInitialData(Connection conn) throws SQLException {
        // 插入默认管理员账户
        insertDefaultAdmin(conn);

        // 插入示例座位
        insertSampleSeats(conn);

        // 插入时间段配置
        insertTimeSlots(conn);
    }

    private static void insertDefaultAdmin(Connection conn) throws SQLException {
        String checkSql = "SELECT COUNT(*) FROM users WHERE student_id = 'admin'";
        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
                ResultSet rs = checkStmt.executeQuery()) {

            if (rs.next() && rs.getInt(1) == 0) {
                String insertSql = "INSERT INTO users (student_id, name, password, user_type) " +
                        "VALUES ('admin', '系统管理员', 'admin123', 1)";
                try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                    insertStmt.executeUpdate();
                }
            }
        }
    }

    private static void insertSampleSeats(Connection conn) throws SQLException {
        String checkSql = "SELECT COUNT(*) FROM seats";
        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
                ResultSet rs = checkStmt.executeQuery()) {

            if (rs.next() && rs.getInt(1) == 0) {
                String insertSql = "INSERT INTO seats (seat_number, floor, area, equipment, has_computer, has_power_socket) VALUES "
                        +
                        "('A001', 1, '阅览区A', '台灯、书架', false, true)," +
                        "('A002', 1, '阅览区A', '台灯、书架', false, true)," +
                        "('A003', 1, '阅览区A', '台灯、书架', false, true)," +
                        "('B001', 1, '阅览区B', '台灯、书架、电脑', true, true)," +
                        "('B002', 1, '阅览区B', '台灯、书架、电脑', true, true)," +
                        "('C001', 2, '安静区C', '台灯、隔音板', false, true)," +
                        "('C002', 2, '安静区C', '台灯、隔音板', false, true)," +
                        "('D001', 2, '讨论区D', '白板、投影', false, true)," +
                        "('E001', 3, 'VIP区E', '台灯、电脑、打印机', true, true)," +
                        "('E002', 3, 'VIP区E', '台灯、电脑、打印机', true, true)";

                try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                    insertStmt.executeUpdate();
                }
            }
        }
    }

    private static void insertTimeSlots(Connection conn) throws SQLException {
        String checkSql = "SELECT COUNT(*) FROM time_slots";
        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
                ResultSet rs = checkStmt.executeQuery()) {

            if (rs.next() && rs.getInt(1) == 0) {
                String insertSql = "INSERT INTO time_slots (slot_name, start_time, end_time) VALUES " +
                        "('上午', '08:00:00', '12:00:00')," +
                        "('下午', '13:00:00', '17:00:00')," +
                        "('晚上', '18:00:00', '22:00:00')";

                try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                    insertStmt.executeUpdate();
                }
            }
        }
    }

    /**
     * 检查数据库是否需要初始化
     */
    public static boolean needsInitialization() {
        try {
            Connection conn = DatabaseConnection.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "users", null);
            return !tables.next(); // 如果users表不存在，则需要初始化
        } catch (Exception e) {
            return true;
        }
    }
}
